﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace QuanLyKhachSan
{
    public class PhongDAO
    {
        public static bool Insert(PhongDTO phongdto)
        {
            int kq;
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "INSERT INTO Phong (tenPhong,maLoaiPhong,tinhTrang,ghiChu,danhDau) VALUES(N'" + phongdto.TenPhong.ToString() + "'," + phongdto.MaLoaiPhong + "," + phongdto.TinhTrang + ",N'" + phongdto.GhiChu.ToString() + "'," + phongdto.DanhDau + ")";
            kq = con.ExecuteNonQuery1(sql);
            if (kq > 0)
            {
                return true;
            }
            return false;
        }

        public static bool Delete(int maPhong)
        {
            int kq;
            DataAccessHelper con = new SqlDataAccessHelper();

            string sqlSelect = "Select maPhong from PhieuThuePhong where maPhong=" + maPhong;
            DataTable dt = con.ExecuteQuery(sqlSelect);

            //string sql = "DELETE FROM Phong WHERE maPhong=" + maPhong;
            string sql = "UPDATE Phong SET danhDau=1 WHERE maPhong=" + maPhong;
            if (dt.Rows.Count <= 0)
            {
                sql = "DELETE FROM Phong WHERE maPhong=" + maPhong;
            }
            kq = con.ExecuteNonQuery1(sql);
            if (kq > 0)
            {
                return true;
            }
            return false;
        }

        public static bool Update(PhongDTO phongdto)
        {
            int kq;
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "UPDATE Phong SET tenPhong=N'" + phongdto.TenPhong.ToString() + "',maLoaiPhong=" + phongdto.MaLoaiPhong + ",ghiChu=N'" + phongdto.GhiChu.ToString() + "' WHERE maPhong=" + phongdto.MaPhong;
            kq = con.ExecuteNonQuery1(sql);
            if (kq > 0)
            {
                return true;
            }
            return false;
        }

        public static DataTable LayDanhSachPhong()
        {
            DataTable dt = new DataTable();
            Dictionary<string, object> para = new Dictionary<string, object>();
            DataAccessHelper help = new SqlDataAccessHelper();

            dt = help.ExecuteProcedure("sp_SelectALLPhong", para);//sp_SelectPhongChuaDanhDau

            return dt;
        }

        public static DataTable LayDanhSachPhongTheoLoai(int maLoaiPhong)
        {
            DataTable dt = new DataTable();
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "SELECT p.maPhong,p.tenPhong ,lp.tenLoaiPhong ,p.tinhTrang,p.ghiChu ,lp.donGia FROM Phong p,LoaiPhong lp WHERE p.maLoaiPhong =lp.maLoaiPhong and p.danhDau=0 and lp.maLoaiPhong=" + maLoaiPhong;
            dt = con.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayDanhSachPhongTheoLoaiCoHieuChinh(int maLoaiPhong)
        {
            DataTable dt = new DataTable();
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "SELECT p.maPhong as [Mã Phòng],p.tenPhong as [Tên Phòng], p.ghiChu as [Ghi Chú],lp.donGia as [Đơn Giá] FROM Phong p,LoaiPhong lp WHERE p.danhDau=0 and p.maLoaiPhong=lp.maLoaiPhong and p.tinhTrang=0 and lp.maLoaiPhong=" + maLoaiPhong;
            dt = con.ExecuteQuery(sql);
            return dt;
        }
        public static int SoNgayThueCuaPhongTrongThang(int maPhong, int thang, int nam)
        {
            Dictionary<string, object> para = new Dictionary<string, object>();
            List<ParameterDirection> paraDir = new List<ParameterDirection>();
            DataAccessHelper help = new SqlDataAccessHelper();

            int kq = 0;
            para.Add("thang", thang);
            paraDir.Add(ParameterDirection.Input);
            para.Add("nam", nam);
            paraDir.Add(ParameterDirection.Input);
            para.Add("maPhong", maPhong);
            paraDir.Add(ParameterDirection.Input);
            para.Add("soNgay", kq);
            paraDir.Add(ParameterDirection.Output);


            List<string> strkq  = help.ExecuteNonProcedure("sp_SoNgayThueCuaPhongTrongThang", para, paraDir);

            kq = int.Parse(strkq[3]);
            return kq;
        }
        public static DataTable LayDanhSachPhongTheoTinhTrang(int tinhTrang)
        {
            DataTable dt = new DataTable();
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "SELECT p.maPhong as [Mã Phòng],p.tenPhong as [Tên Phòng], p.ghiChu as [Ghi Chú],lp.donGia as [Đơn Giá] FROM Phong p,LoaiPhong lp WHERE p.danhDau=0 and p.maLoaiPhong=lp.maLoaiPhong and p.tinhTrang=" + tinhTrang;
            dt = con.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayDanhSachPhongDaThue(string tenPhong)
        {
            DataTable dt = new DataTable();
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "SELECT p.maPhong as [Mã Phòng],p.tenPhong as [Phòng],DATEDIFF(DAY,pt.ngayThue,GETDATE())+1 as [Số Ngày Thuê],pt.donGiaThue*pt.heSoThue+pt.donGiaThue*pt.phuThuThue as [Đơn Giá],(DATEDIFF(DAY,pt.ngayThue,GETDATE())+1)*pt.donGiaThue*pt.heSoThue+pt.donGiaThue*pt.phuThuThue as[Thành Tiền] FROM Phong p, PhieuThuePhong pt WHERE p.maPhong = pt.maPhong and p.danhDau=0 and p.tinhTrang=1 and p.tenPhong like N'" + tenPhong.ToString() + "%' and pt.maPhieuThue not in ( select ct.maPhieuThue from ChiTietHoaDon ct)";
            dt = con.ExecuteQuery(sql);
            return dt;
        }
        public static bool TraPhong(int maPhong)
        {
            int kq;
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "UPDATE Phong SET tinhTrang=0 WHERE maPhong=" + maPhong;
            kq = con.ExecuteNonQuery1(sql);
            if (kq > 0)
            {
                return true;
            }
            return false;
        }
        public static bool ThuePhong(int maPhong)
        {
            int kq;
            DataAccessHelper con = new SqlDataAccessHelper();
            string sql = "UPDATE Phong SET tinhTrang=1 WHERE maPhong=" + maPhong;
            kq = con.ExecuteNonQuery1(sql);
            if (kq > 0)
            {
                return true;
            }
            return false;
        }

        // Tim kiem theo loai phong
        public static DataTable DanhSachPhongTraCuu(LoaiPhongDTO loaiPhongdto, PhongDTO phongdto)
        {
            DataTable dt = new DataTable();
            // [MãPhòng]=maPhong,[TênPhòng]=tenPhong,[MãLoạiPhòng]=maLoaiPhong,[TìnhTrạng]=tinhTrang,[GhiChú]=ghiChu
            string sql = "select * from Phong p, LoaiPhong lp where p.maLoaiPhong = lp.maLoaiPhong ";
            
            if (string.IsNullOrEmpty(loaiPhongdto.TenLoaiPhong) == false)
            {
                sql = sql + " and p.maLoaiPhong="+ loaiPhongdto.MaLoaiPhong ;
            }
            if (string.IsNullOrEmpty(phongdto.TenPhong) == false)
            {
                sql = sql + " and tenPhong like N'" + phongdto.TenPhong + "%' ";
            }
            if (loaiPhongdto.DonGia != 0)
            {
                sql = sql + " and lp.donGia =" + loaiPhongdto.DonGia;
            }
            sql = sql + " and p.tinhTrang=" + phongdto.TinhTrang + " and p.danhDau=0";

            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
    }
}
